﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL;
using KPIS.GERP.GHRMS.IDAL;
using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class CommandBLL : ICommandBLL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        private static readonly ICommandDAL DAL = DataAccess.CreateCommands();

        private CommandInfo cmdInfo = new CommandInfo();
        private CommandTypeInfo cmdType = new CommandTypeInfo();
        private EmployeeTypeInfo empType = new EmployeeTypeInfo();

        public IList<CommandInfo> ListCommand(string TranType)
        {
            if (TranType.Length > 0)
                cmdInfo.TranType = TranType;

            return DAL.ListCommandInfo(cmdInfo);
        }

        public IList<CommandInfo> ListCommand(string TranType, Nullable<int> Sequence
            , Nullable<int> CommandType, Nullable<int> EmployeeType)
        {
            if (TranType.Length > 0)
                cmdInfo.TranType = TranType;
            if (Sequence.HasValue)
                cmdInfo.Sequence = (int)Sequence;
            if (CommandType.HasValue)
                cmdType.Sequence = (int)CommandType;
            if (EmployeeType.HasValue)
            {
                empType.Sequence = (int)EmployeeType;
                cmdInfo.EmployeeType = empType;
            }

            cmdInfo.CmdType = cmdType;
            return DAL.ListCommandInfo(cmdInfo);
        }
        public DataSet LoadCommands(string TranType, Nullable<int> Sequence, Nullable<int> CommandType, Nullable<int> EmployeeType)
        {
            if (TranType.Length > 0)
                cmdInfo.TranType = TranType;
            if (Sequence.HasValue)
                cmdInfo.Sequence = (int)Sequence;
            if (CommandType.HasValue)
                cmdType.Sequence = (int)CommandType;
            if (EmployeeType.HasValue)
            {
                empType.Sequence = (int)EmployeeType;
                cmdInfo.EmployeeType = empType;
            }

            cmdInfo.CmdType = cmdType;
            return DAL.LoadCommands(cmdInfo);
        }
        public string GetNextDraftNo(string CommandTypeSeq)
        {
            return DAL.GetNextDraftNo(CommandTypeSeq);
        }
        public void Insert(string TranType, string CmdFlag, string DraftNo, string RefNo, string Descriptions
            , string Status, Nullable<int> ApprovedBy, string ApprovedName, string ApprovedPositions
            , Nullable<DateTime> ApprovedDate, string Remarks, int CreatedBy)
        {
            try
            {
                cmdInfo.TranType = TranType;
                cmdInfo.CmdFlag = CmdFlag;
                cmdInfo.DraftNo = DraftNo;
                cmdInfo.RefNo = RefNo;
                cmdInfo.Descriptions = Descriptions;
                cmdInfo.Status = Status;
                cmdInfo.ApprovedBy = ApprovedBy;
                cmdInfo.ApprovedName = ApprovedName;
                cmdInfo.ApprovedPositions = ApprovedPositions;
                cmdInfo.ApprovedDate = ApprovedDate;
                cmdInfo.Remarks = Remarks;
                cmdInfo.CreatedBy = CreatedBy;

                DAL.Insert(cmdInfo);

            }
            catch
            {
                throw new Exception("");
            }
        }
        public void InsertCommandEmployee(string TranType, string CmdFlag, string DraftNo, string RefNo
            , string Name, string Descriptions, string ReportHeader
            , string Status, int EmployeeType, int CommandType, Nullable<int> ApprovedBy, string ApprovedName
            , string ApprovedPositions, Nullable<DateTime> ApprovedDate, string Remarks, int CreatedBy)
        {
            try
            {
                cmdType.Sequence = CommandType;
                empType.Sequence = EmployeeType;

                cmdInfo.CmdType = cmdType;
                cmdInfo.EmployeeType = empType;
                cmdInfo.TranType = TranType;
                cmdInfo.CmdFlag = CmdFlag;
                cmdInfo.DraftNo = DraftNo;
                cmdInfo.RefNo = RefNo;
                cmdInfo.Name = Name;
                cmdInfo.Descriptions = Descriptions;
                cmdInfo.Status = Status;
                cmdInfo.ApprovedBy = ApprovedBy;
                cmdInfo.ApprovedName = ApprovedName;
                cmdInfo.ApprovedPositions = ApprovedPositions;
                cmdInfo.ApprovedDate = ApprovedDate;
                cmdInfo.ReportHeader = ReportHeader;
                cmdInfo.Remarks = Remarks;
                cmdInfo.CreatedBy = CreatedBy;

                DAL.Insert(cmdInfo);
            }
            catch
            {
                throw new Exception("");
            }
        }
        public void Update(string TranType, int Sequence, string CmdFlag, string DraftNo, string RefNo, string Descriptions
            , string Status, Nullable<int> ApprovedBy, string ApprovedName, string ApprovedPositions
            , Nullable<DateTime> ApprovedDate, string Remarks, int UpdatedBy)
        {
            try
            {
                cmdInfo.TranType = TranType;
                cmdInfo.Sequence = Sequence;
                cmdInfo.CmdFlag = CmdFlag;
                cmdInfo.DraftNo = DraftNo;
                cmdInfo.RefNo = RefNo;
                cmdInfo.Descriptions = Descriptions;
                cmdInfo.Status = Status;
                cmdInfo.ApprovedBy = ApprovedBy;
                cmdInfo.ApprovedName = ApprovedName;
                cmdInfo.ApprovedPositions = ApprovedPositions;
                cmdInfo.ApprovedDate = ApprovedDate;
                cmdInfo.Remarks = Remarks;
                cmdInfo.UpdatedBy = UpdatedBy;

                DAL.Update(cmdInfo);
            }
            catch
            {
                throw new Exception("");
            }
        }
        public void UpdateCommandEmployee(string TranType, int Sequence, string CmdFlag, string DraftNo
            , string RefNo, string Name, string Descriptions, string ReportHeader
            , string Status, int EmployeeType, int CommandType, Nullable<int> ApprovedBy, string ApprovedName
            , string ApprovedPositions, Nullable<DateTime> ApprovedDate, string Remarks, int UpdatedBy)
        {
            try
            {
                cmdType.Sequence = CommandType;
                empType.Sequence = EmployeeType;

                cmdInfo.Sequence = Sequence;
                cmdInfo.CmdType = cmdType;
                cmdInfo.EmployeeType = empType;
                cmdInfo.TranType = TranType;
                cmdInfo.CmdFlag = CmdFlag;
                cmdInfo.DraftNo = DraftNo;
                cmdInfo.RefNo = RefNo;
                cmdInfo.Name = Name;
                cmdInfo.Descriptions = Descriptions;
                cmdInfo.Status = Status;
                cmdInfo.ApprovedBy = ApprovedBy;
                cmdInfo.ApprovedName = ApprovedName;
                cmdInfo.ApprovedPositions = ApprovedPositions;
                cmdInfo.ApprovedDate = ApprovedDate;
                cmdInfo.ReportHeader = ReportHeader;
                cmdInfo.Remarks = Remarks;
                cmdInfo.UpdatedBy = UpdatedBy;

                DAL.Update(cmdInfo);
            }
            catch
            {
                throw new Exception("");
            }
        }
        public void Delete(string TranType, int Sequence, int UpdatedBy)
        {
            try
            {
                DAL.Delete(TranType, Sequence, UpdatedBy);
            }
            catch
            {
                throw new Exception("");
            }
        }
        public DataSet LoadCommandType(string CmdGroup)
        {
            return DAL.LoadCommandType(CmdGroup);
        }
        //public DataTable SelectCommandType(string Group)
        //{
        //    try
        //    {
        //        string sql;
        //        string Filter = "";

        //        if (Group == "ADD")
        //            Filter = " AND CMD_GROUP_SEQ = 1";
        //        else if (Group == "GROW")
        //            Filter = " AND CMD_GROUP_SEQ = 2";
        //        else if (Group == "MOVE")
        //            Filter = " AND CMD_GROUP_SEQ IN(3, 4)";
        //        else if (Group == "GROWMOVE")
        //            Filter = " AND CMD_GROUP_SEQ IN(2,3,4)";                

        //        dbManager.Open();
        //        dbManager.CreateParameters(1);

        //        dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                
        //        sql = " SELECT * FROM HRM_PS_COMMAND_TYPES "
        //            + " WHERE RECORD_STATUS = @RecordStatus"
        //                + Filter;

        //        DataSet ds = new DataSet();
        //        DataTable dt;
        //        ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //        dt = ds.Tables[0].Copy();
        //        return dt;
        //    }

        //    catch (Exception ex)
        //    {
        //        return null;
        //    }

        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //}

        public DataTable Select(string Group, string CmdType)
        {
            try
            {
                
                string sql;
                string Filter = "";

                if (Group == "ADD")
                    Filter = " AND type.CMD_GROUP_SEQ = 1";
                else if (Group == "GROW")
                    Filter = " AND type.CMD_GROUP_SEQ = 2";
                else if (Group == "MOVE")
                    Filter = " AND type.CMD_GROUP_SEQ IN(3, 4)";
                else if (Group == "GROWMOVE")
                    Filter = " AND type.CMD_GROUP_SEQ IN(2,3,4)";
                else if (Group == "QUIT")//ลาออก
                    Filter = " AND type.CMD_GROUP_SEQ IN(6)";
                else if (Group == "TRANSFER")// ให้โอน
                    Filter = " AND cmd.CMD_TYPE_SEQ = 305";
                else if (Group == "CMT")//แต่งตั้งคณะกรรมการ
                    Filter = " AND type.CMD_GROUP_SEQ = 9";

                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@CmdType", CmdType);

                sql = " SELECT cmd.*"
                    + ", grp.CMD_GROUP_NAME, type.CMD_TYPE_NAME, grp.CMD_GROUP_SEQ,"
                    + " CASE cmd.CMD_TYPE_SEQ"
                        + " WHEN 101 THEN 'RC_PlacementByExam'"//บรรจุผู้สอบแข่งขันได้
                        + " WHEN 102 THEN 'RC_PlacementByExam'"//บรรจุผู้ได้รับคัดเลือก
                        + " WHEN 105 THEN 'RC_TransferExam'"
                        + " WHEN 106 THEN 'RC_BackOfficialSoldier'"
                        + " WHEN 107 THEN 'RC_BackOfficial'"
                        + " WHEN 108 THEN 'RC_BackOfficialOld'"
                        + " WHEN 201 THEN 'RC_CmdMove'" //'RC_MoveOfficialHS'" ย้าย
                        + " WHEN 202 THEN 'RC_CmdMove'" //RC_MoveOfficialPlus ย้ายข้าราชการซึ่งได้รับวุฒิเพิ่ม
                        + " WHEN 203 THEN 'RC_BackSamePosition'"
                        + " WHEN 301 THEN 'RC_PlacementByExam'"//'RC_TransferOfficial'"//รับโอน
                        + " WHEN 302 THEN 'RC_PlacementByExam'"//'RC_TransferQualOfficialPlus'"//
                        + " WHEN 303 THEN 'RC_PlacementByExam'"//'RC_TransferOfficialPlus'"//
                        + " WHEN 304 THEN 'RC_PlacementByExam'"//'RC_TransferExam'"//รับโอนผู้สอบแข่งขันได้
                        + " WHEN 401 THEN 'RC_PromoteOfficial'"//
                        + " WHEN 501 THEN 'RC_MoveOfficial'"//
                        + " WHEN 502 THEN 'RC_TransferOfficialManage'"//
                        + " WHEN 503 THEN 'RC_BackManageOld'"//
                        + " ELSE 'RC_PlacementByExam' END AS FROM_NAME"
                        + ", rc.CMD_STATUS_NAME"
                        + ", CASE WHEN cmd.CMD_STATUS = 'W' "
                            + " THEN cmd.CMD_DRAFT_NO "
                            + " ELSE cmd.CMD_REF_NO END AS CMD_NO"
                    + ", CASE WHEN cmd.APPROVED_SEQ IS NULL THEN cmd.APPROVED_NAME"
                        + " ELSE CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME)"
                        + " END AS APPROVED_TEXT"
                    + " FROM HRM_PS_COMMANDS cmd"
                    + " LEFT JOIN HRM_PS_COMMAND_TYPES type ON cmd.CMD_TYPE_SEQ = type.CMD_TYPE_SEQ"
                    + " LEFT JOIN HRM_PS_COMMAND_GROUPS grp ON type.CMD_GROUP_SEQ = grp.CMD_GROUP_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON cmd.APPROVED_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " LEFT JOIN MAS_RECORD_STATUSES rc ON cmd.CMD_STATUS = rc.RECORD_STATUS"
                    + " WHERE cmd.RECORD_STATUS not like @RecordStatus"
                    + (string.IsNullOrEmpty(CmdType) ? "" : " AND cmd.CMD_TYPE_SEQ = @CmdType")
                    + Filter;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertCmdCommittee(string cmd_flag, string cmd_draft_no, string cmd_name, string cmd_ref_no
            , int cmd_type_seq, int emp_type_seq, string cmd_status, Nullable<int> approved_seq
            , string approved_name, string approved_positions, Nullable<DateTime> approved_date
            , int confirm, int inv_type_seq, string remarks, string CreatedBy)
        {
            try
            {
                dbManager.CreateParameters(17);
                dbManager.AddParameters(0, "@cmd_flag", cmd_flag);
                dbManager.AddParameters(1, "@cmd_draft_no", cmd_draft_no);
                dbManager.AddParameters(2, "@cmd_name", cmd_name);
                dbManager.AddParameters(3, "@cmd_ref_no", cmd_ref_no);
                dbManager.AddParameters(4, "@cmd_type_seq", cmd_type_seq);
                dbManager.AddParameters(5, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(6, "@cmd_status", cmd_status);
                dbManager.AddParameters(7, "@approved_seq", approved_seq);
                dbManager.AddParameters(8, "@approved_name", approved_name);
                dbManager.AddParameters(9, "@approved_positions", approved_positions);
                dbManager.AddParameters(10, "@approved_date", approved_date);
                dbManager.AddParameters(11, "@confirm", confirm);
                dbManager.AddParameters(12, "@inv_type_seq", inv_type_seq);
                dbManager.AddParameters(13, "@remarks", remarks);
                dbManager.AddParameters(14, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(15, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(16, "@CreatedWhen", DateTime.Today);

                dbManager.Open();
                string strSQL;
                strSQL = "INSERT INTO HRM_PS_COMMANDS"
                        + "(CMD_FLAG, CMD_DRAFT_NO, CMD_NAME, CMD_REF_NO, CMD_TYPE_SEQ, EMP_TYPE_SEQ"
                        + ", CMD_STATUS, APPROVED_SEQ, APPROVED_NAME, APPROVED_POSITIONS, APPROVED_DATE"
                        + ", CONFIRM, INV_TYPE_SEQ, REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES (@cmd_flag, @cmd_draft_no, @cmd_name, @cmd_ref_no, @cmd_type_seq, @emp_type_seq"
                        + ", @cmd_status, @approved_seq, @approved_name, @approved_positions, @approved_date"
                        + ", @confirm, @inv_type_seq, @remarks, @record_status, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
        public void UpdateCmdCommittee(int Sequence, string cmd_flag, string cmd_draft_no, string cmd_name
            , string cmd_ref_no, int cmd_type_seq, int emp_type_seq, string cmd_status, Nullable<int> approved_seq
            , string approved_name, string approved_positions, Nullable<DateTime> approved_date
            , int confirm, int inv_type_seq, string remarks, string UpdateBy)
        {
            try
            {
                dbManager.CreateParameters(17);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@cmd_flag", cmd_flag);
                dbManager.AddParameters(2, "@cmd_draft_no", cmd_draft_no);
                dbManager.AddParameters(3, "@cmd_name", cmd_name);
                dbManager.AddParameters(4, "@cmd_ref_no", cmd_ref_no);
                dbManager.AddParameters(5, "@cmd_type_seq", cmd_type_seq);
                dbManager.AddParameters(6, "@emp_type_seq", emp_type_seq);
                dbManager.AddParameters(7, "@cmd_status", cmd_status);
                dbManager.AddParameters(8, "@approved_seq", approved_seq);
                dbManager.AddParameters(9, "@approved_name", approved_name);
                dbManager.AddParameters(10, "@approved_positions", approved_positions);
                dbManager.AddParameters(11, "@approved_date", approved_date);
                dbManager.AddParameters(12, "@confirm", confirm);
                dbManager.AddParameters(13, "@remarks", remarks);
                dbManager.AddParameters(14, "@inv_type_seq", inv_type_seq);
                dbManager.AddParameters(15, "@updated_by", UpdateBy);
                dbManager.AddParameters(16, "@updated_when", DateTime.Today);
                dbManager.Open();

                dbManager.ExecuteNonQuery(CommandType.Text
                    , "UPDATE HRM_PS_COMMANDS"
                    + " SET CMD_FLAG = @cmd_flag"
                    + ", CMD_DRAFT_NO = @cmd_draft_no"
                    + ", CMD_NAME = @cmd_name"
                    + ", CMD_REF_NO = @cmd_ref_no"
                    + ", CMD_TYPE_SEQ = @cmd_type_seq"
                    + ", EMP_TYPE_SEQ = @emp_type_seq"
                    + ", CMD_STATUS = @cmd_status"
                    + ", APPROVED_SEQ = @approved_seq"
                    + ", APPROVED_NAME = @approved_name"
                    + ", APPROVED_POSITIONS = @approved_positions"
                    + ", APPROVED_DATE = @approved_date"
                    + ", CONFIRM = @confirm"
                    + ", INV_TYPE_SEQ = @inv_type_seq"
                    + ", REMARKS = @remarks"
                    //+ ", RECORD_STATUS = @RecordStatus"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                + " WHERE CMD_SEQ = @sequence");
                dbManager.Dispose();
            }
            catch
            {
                throw;
            }
        }
    }
}
